﻿using System;
using System.Collections.Generic;
using System.Text;
using Musicman.Common.Entities;
using System.Data;
using Musicman.Common;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace Musicman.DAL
{
    public class HistoryFileDAO : DAOBase
    {
        # region singleton definition
        static readonly HistoryFileDAO instance = new HistoryFileDAO();

        public static HistoryFileDAO Instance
        {
            get
            {
                return instance;
            }
        }
        static HistoryFileDAO()
        {
        }
        # endregion

        #region select 
        private string selectSQL = @"SELECT {0} UserHistory.UserHistoryID, UserHistory.PersonID, 
                                                      UserHistory.SearchFileID, UserHistory.Name, 
                                                      UserHistory.DateDownloaded
                                                   {1} 
                                                   FROM UserHistory {2} {3}";

        public List<HistoryFile> LoadSearchfiles(string where, string orderby, IEnumerable<SelectParameter> parameters)
        {
            return LoadSearchfiles(0, where, "", orderby, parameters);
        }

        public List<HistoryFile> LoadSearchfiles(int top, string where, string additional, string orderby, IEnumerable<SelectParameter> parameters)
        {
            List<HistoryFile> res = new List<HistoryFile>();
            string strTop = top == 0 ? "" : String.Format(" TOP {0}", top);
            string sql = String.Format(selectSQL, strTop, additional, where, orderby);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(sql);

                foreach (SelectParameter parameter in parameters)
                {
                    db.AddInParameter(cmd, parameter.ParameterName, parameter.DbType, parameter.Value);
                }

                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        HistoryFile sf = CreateHistoryFile(reader);
                        res.Add(sf);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public HistoryFile CreateHistoryFile(IDataReader reader)
        {
            HistoryFile res = new HistoryFile();
            res.UserHistoryID = reader.GetInt64(0);
            res.PersonID = reader.GetInt64(1);
            res.SearchFileID = reader.IsDBNull(2) ? null : (long?)reader.GetInt64(2);
            res.FileName = reader.GetString(3);
            res.DownloadDate = reader.GetDateTime(4);
            return res;
        }


        #endregion
    }
}
